import time
import json
import pandas as pd
import openpyxl
from PyQt5.QtCore import QObject, pyqtSignal, QDateTime


class ParseCourse(QObject):
    # 设置最大进度条信号
    progress_max_sig = pyqtSignal(int)
    # 进度条修改信号
    parse_progress_sig = pyqtSignal(int)
    # 选择的班级
    chkClass = []
    # 异常数据
    except_data = []
    # 目标数据
    dst_df = None
    # 表格拆分进度
    # first_seg_progress_v = 0

    def __init__(self):
        super().__init__()
        # 结果数据df
        self.dst_df = self.create_class_df()

    def clear_data(self):
        self.chkClass.clear()
        self.except_data.clear()
        self.dst_df.drop(self.dst_df.index, inplace=True)

    # 创建空的dataframe
    def create_class_df(self):
        col = ['周次', '星期', '节次', '课程名称', '上课班级', '上课地点', '授课教师']
        df = pd.DataFrame(data=None, columns=col)
        return df

    # 新文件名
    def gen_new_file_name(self):
        datetime = QDateTime.currentDateTime()
        name = datetime.toString('yyyyMMddHHmmss')
        print('new excel file name:', name)
        return name

    # 拆分所有的合并单元格，并赋予合并之前的值。
    # 由于openpyxl并没有提供拆分并填充的方法，所以使用该方法进行完成
    def unmerge_and_fill_cells(self, worksheet):
        all_merged_cell_ranges = list(worksheet.merged_cells.ranges)
        for merged_cell_range in all_merged_cell_ranges:
            merged_cell = merged_cell_range.start_cell
            worksheet.unmerge_cells(range_string=merged_cell_range.coord)
            for row_index, col_index in merged_cell_range.cells:
                cell = worksheet.cell(row=row_index, column=col_index)
                cell.value = merged_cell.value

    # 读取原始xlsx文件，拆分并填充单元格，然后生成中间临时文件。
    def unmerge_cell(self, filename):
        wb = openpyxl.load_workbook(filename)
        for sheet_name in wb.sheetnames:
            sheet = wb[sheet_name]
            self.unmerge_and_fill_cells(sheet)
        filename = filename.replace(".xls", "_temp.xls")
        wb.save(filename)
        wb.close()
        return filename

    # 处理所有excel文件所有sheet
    def handle_sheet(self, file_name):
        pf = pd.ExcelFile(file_name)
        prg_v = 0
        for name in pf.sheet_names:
            if len(self.chkClass) > 0:
                if name not in self.chkClass:
                    continue
            df_pre = pf.parse(sheet_name=name, header=None)
            self.get_class_info(name, df_pre)
            prg_v += 1
            self.parse_progress_sig.emit(prg_v)

        self.write_to_file()
        self.write_except_file()

    # 获取排课信息
    def get_class_info(self, skbj, d):
        r_start, r_end, r_step = 3, 12, 2
        c_start, c_end, c_step = 2, 8, 1
        # d = pd.read_excel(file_name, header=None)
        for c in range(c_start, c_end + 1):
            # 隔行取值
            for r in range(r_start, r_end + 1, 2):
                if pd.isna(d[c][r]):
                    continue
                self.add_df_line(self.parse_class(skbj, c, d[1][r], d[c][r]))

    # 写入文件
    def write_except_file(self):
        with open("except_record.json", "w", encoding='utf-8') as f:
            json.dump(self.except_data, f, indent=4, ensure_ascii=False)
            print("异常文件写入完成...")

    # 写入文件
    def write_to_file(self):
        self.dst_df.to_excel('课表' + self.gen_new_file_name() + '.xlsx', index=False)

    # 生成excel行数据
    def add_df_line(self, cls_arr):
        c = ['zc', 'xq', 'jc', 'kcmc', 'skbj', 'skdd', 'skjs']
        for cls in cls_arr:
            r = []
            for i in range(0, 7):
                r.append(cls[c[i]])
            self.dst_df.loc[len(self.dst_df)] = r

    # 添加异常数据
    def add_except_data(self, skbj, xq, jc, cstr):
        self.except_data.append({
            '上课班级': skbj,
            '星期': ['一', '二', '三', '四', '五', '六', '七'][xq - 2],
            '节次': str(jc) + '-' + str(jc + 1),
            '课程信息': cstr
        })

    # 解析单元格数据
    def parse_class(self, skbj, xq, jc, cstr):
        arr = cstr.split('\n')
        flag = len(arr) % 4

        if flag != 0:
            print('-------有非标准数据-------')
            print(cstr)
            print('-----------end----------')
            self.add_except_data(skbj, xq, jc, cstr)
            return []

        items = len(arr) // 4
        start_index = 0
        r = []
        for i in range(0, items):
            d = dict()
            d['kcmc'] = arr[start_index]
            start_index += 1
            d['zc'] = arr[start_index].lstrip('(').rstrip(')')
            start_index += 1
            d['skjs'] = arr[start_index]
            start_index += 1
            d['skdd'] = arr[start_index]
            start_index += 1

            d['xq'] = ['一', '二', '三', '四', '五', '六', '七'][xq - 2]
            d['jc'] = str(jc) + '-' + str(jc + 1)
            d['skbj'] = skbj

            r.append(d)
        return r

    # def test(self):
    #     self.progress_max_sig.emit(200)
    #     time.sleep(2)
    #     self.parse_progress_sig.emit(50)
